/**
 * 
 */
package com.yunyike.utils;

import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Picture;
import org.apache.poi.ss.usermodel.PictureData;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.yunyike.constants.CommonConstant;
import com.yunyike.constants.ExcelConstants;
import com.yunyike.dto.MerchandiseDTO;
import com.yunyike.dto.QuotationMerchandiseDTO;
import com.yunyike.model.Merchandise;
import com.yunyike.model.Price;
import com.yunyike.model.Quotation;
import com.yunyike.model.Sku;

public class QuotationMerchandiseExcelUtil {

	
	
	public static String readMiddleExcel(String middleexcel, 
			List<QuotationMerchandiseDTO> list,String skuStatuParam,String statusParam) throws Exception {
		String emfStr = "";
		try {
			Workbook book = null;
			if(middleexcel.indexOf(".xlsx")==-1){
				book = new HSSFWorkbook(new FileInputStream(middleexcel));
			}else{
				book = new XSSFWorkbook(new FileInputStream(middleexcel));
			}
			Sheet sheet = book.getSheetAt(0);
			
			boolean changePicFlag = false;
			boolean showPicFlag = false;
			for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
				QuotationMerchandiseDTO dto = new QuotationMerchandiseDTO();
				String status = ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT23);
				if(!StringUtils.isBlank(statusParam)&&!statusParam.equals(status)){
					continue;
				}
				String skustatus = ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT0);
				if(!StringUtils.isBlank(skuStatuParam)&&!skuStatuParam.equals(skustatus)){
					continue;
				}
				String articleNum = ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT1);
				String rate = ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT6);
				String europePrice = ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT7);
				String europePricermb = ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT8);
				String officePrice = ExcelUtils.getValue(sheet.getRow(rowNum),ExcelConstants.INT9);
				String discount = ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT10);
				String jdPrice = ExcelUtils.getValue(sheet.getRow(rowNum),ExcelConstants.INT11);
				String skuPrice = ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT12);
				String discountPrice = ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT14);
				String ratio1 = ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT13);
				String ratio2 = ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT15);
				String picPath = ExcelUtils.getValue(sheet.getRow(rowNum),ExcelConstants.INT3);
				/*if("".equals(articleNum)){
					break;
				}*/
				dto.setStatus(status==""?0:Integer.parseInt(status));
				dto.setSkuStatus(skustatus==""?0:Integer.parseInt(skustatus));
				dto.setArticleNumber(articleNum);
				dto.setName(ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT2));
				if(!"".equals(picPath)){
					dto.setPicture(picPath);
					if(picPath.indexOf(ExcelConstants.PIC_EMF_EXT)!= -1){
						String newPicPath = picPath.replace(ExcelConstants.PIC_EMF_EXT, ExcelConstants.PIC_EXT);
						File file = new File(ExcelConstants.EXCEL_MIDDLEPATH_PIC+newPicPath);
						if(file.exists()){
							dto.setPicture(newPicPath);
							sheet.getRow(rowNum).getCell(ExcelConstants.INT3).setCellValue(newPicPath);
							changePicFlag = true;
						}else{
							showPicFlag = true;
						}
					}
				}
				dto.setQty(ExcelUtils.getValue(sheet.getRow(rowNum),ExcelConstants.INT4));
				dto.setRetail(ExcelUtils.getValue(sheet.getRow(rowNum),ExcelConstants.INT5));
				dto.setRate(rate==""?0:Double.parseDouble(rate));
				dto.setEuropePrice(europePrice==""?0:Double.parseDouble(europePrice));
				dto.setEuropePriceRmb(europePricermb==""?0:Double.parseDouble(europePricermb));
				dto.setOfficePrice(officePrice==""?0:Double.parseDouble(officePrice));
				dto.setEuropeOfficeDiscount(discount==""?0:Double.parseDouble(discount));
				dto.setJingdongPrice(jdPrice==""?0:Double.parseDouble(jdPrice));
				dto.setSkuPrice(skuPrice==""?0:Double.parseDouble(skuPrice));
				dto.setDiscountPrice(discountPrice==""?0:Double.parseDouble(discountPrice));
				dto.setRatio1(ratio1==""?0:Double.parseDouble(ratio1));
				dto.setRatio2(ratio2==""?0:Double.parseDouble(ratio2));
				dto.setReferenceInternetUrl(ExcelUtils.getValue(sheet.getRow(rowNum),ExcelConstants.INT16));
				dto.setMerchandiseName(ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT17));
				dto.setMerchandiseColor(ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT18));
				dto.setMerchandiseCollection(ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT19));
				dto.setMerchandiseSpecDesc(ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT20));
				dto.setMerchandiseDesc(ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT21));
				dto.setRemark(ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT22));
				dto.setRowIndex(rowNum);
				String qty = ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT27);
				if(!StringUtils.isBlank(qty))dto.setSummarySkuQty(Integer.parseInt(qty));
				list.add(dto);
			}
			if(changePicFlag){
				FileOutputStream fileOut = new FileOutputStream(middleexcel);  
				book.write(fileOut);  
	            fileOut.close();  
			}
			if(showPicFlag){//存在emp格式的且未转换的图片，则返回emp图片地址。
				emfStr = middleexcel.replace(ExcelConstants.EXCEL_MIDDLEPATH, ExcelConstants.EXCEL_MIDDLEPATH_PIC)+"/";
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception("readMiddleExcel 发生异常");
		}
		return emfStr;
	}
	
	/**
	 * 生成中间excel
	 * @param quotMerchlist
	 * @param workName
	 * @param path
	 * @throws Exception
	 */
	public static String writeMiddleExcel(List<QuotationMerchandiseDTO> quotMerchlist,
			String workName,String path) throws Exception {
		String emfStr ="";
		try {
			String[] headTitle = {"囤货状态",ExcelConstants.ARTICLE_NUMBER,ExcelConstants.NAME,ExcelConstants.PIC,ExcelConstants.QTY,ExcelConstants.RETAIL,
								"汇率","欧洲折后价€","欧洲官价¥","国内官价¥","折扣（相比国内官价）¥","京东价¥",
								"预定¥","系数1","全款价¥","系数2","参考网址",
								"商品名称","颜色","款式","规格","介绍","备注","发布状态","merchandiseId","skuId","priceId","summarySkuQty"};
			
			Map<String, Object> workMap =null;
			if(workName.indexOf(".xlsx")==-1){
				workMap = ExcelUtils.geneHSSFBookBegin(workName, headTitle);
			}else{
				workMap = ExcelUtils.geneXSSFBookBegin(workName, headTitle);
			}
			
			Workbook workbook = (Workbook) workMap.get("workbook");
			Sheet sheet = (Sheet) workMap.get("sheet");
			Row row = (Row) workMap.get("row");
			
			boolean emfFlag = false;
			for (int i = 0; i < quotMerchlist.size(); i++) {
				QuotationMerchandiseDTO exportDTO = quotMerchlist.get(i);
				CellStyle styleRow = workbook.createCellStyle();
				styleRow.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
				row = sheet.createRow(i + 1);
				ExcelUtils.geneCell(row, ExcelConstants.SHORT0, CommonConstant.SKU_UNTREATED+"", styleRow);
				ExcelUtils.geneCell(row, ExcelConstants.SHORT1, exportDTO.getArticleNumber(), styleRow);
				ExcelUtils.geneCell(row, ExcelConstants.SHORT2, exportDTO.getName(), styleRow);
				ExcelUtils.geneCell(row,ExcelConstants.SHORT3, exportDTO.getPicture(), styleRow);
				if(exportDTO.getPicture()!=null&&
						exportDTO.getPicture().indexOf(ExcelConstants.PIC_EMF_EXT)!= -1){
					emfFlag =true;
				}
				ExcelUtils.geneCell(row,ExcelConstants.SHORT4, exportDTO.getQty(), styleRow);
				ExcelUtils.geneCell(row,ExcelConstants.SHORT5, exportDTO.getRetail(), styleRow);
				ExcelUtils.geneCell(row,ExcelConstants.SHORT23, exportDTO.getStatus()+"", styleRow);
			}
			File file = new File(ExcelConstants.EXCEL_MIDDLEPATH);
			if(!file.exists()){
				file.mkdirs();
			}
			 FileOutputStream fileOut = new FileOutputStream(ExcelConstants.EXCEL_MIDDLEPATH+workName);  
			 workbook.write(fileOut);  
             fileOut.close();  
             if(emfFlag){
            	 emfStr =  ExcelConstants.EXCEL_MIDDLEPATH_PIC + workName+"/";
 			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception("writeMiddleExcel 发生异常");
		}
		return emfStr;
	}
  
	/**
	 * 从原Excel提取需要给dto
	 * @param excellink
	 * @param excelname
	 * @param quotMerchlist
	 * @return
	 */
	public static  String readExcelToMerchList(String excellink,String excelname,List<QuotationMerchandiseDTO> quotMerchlist){
		
		try {
			Workbook book = null;
			try {
				if(excelname.indexOf(".xlsx")==-1){
					book = new HSSFWorkbook(new FileInputStream(excellink + excelname));
				}else{
					book = new XSSFWorkbook(new FileInputStream(excellink + excelname));
				}
			} catch (Exception ex) {
				ex.printStackTrace();
				return "excel不存在或文件破损";
			}
			if(book==null){
				return "excel不存在";
			}
			
			Sheet sheet = book.getSheetAt(0);
			
			Map<String, PictureData>  picMap = null;
			if(excelname.indexOf(".xlsx")==-1){
				picMap = ExcelUtils.getSheetPictrues03(0, (HSSFSheet)sheet,(HSSFWorkbook)book,excelname);
			}else{
				picMap = ExcelUtils.getSheetPictrues07(0, (XSSFSheet)sheet,(XSSFWorkbook)book,excelname);
			}
			if(picMap!=null&&picMap.size()>0)ExcelUtils.printImg(picMap,excelname);
			
			int titleRow = 0;
//			处理第一行，头
			Row hssfRow = null;
			int article_number_col =-1;
			int name_col =-1;
			int pic_col = -1;
			int retail_col = -1;
			int qty_col = -1;
			for (int rowNum = 0; rowNum < 10; rowNum++) {
				hssfRow = sheet.getRow(rowNum);
				if (hssfRow == null||hssfRow.getPhysicalNumberOfCells()==0) {
//					return "excel为空";
					continue;
				}else{
					for(int col = 0;col<hssfRow.getPhysicalNumberOfCells();col++){
						String content = ExcelUtils.getValue(hssfRow, col);
						if(ExcelConstants.ARTICLE_NUMBER.equals(content)){
							article_number_col=col;
							titleRow = rowNum;
						}
						if(ExcelConstants.NAME.equals(content))name_col=col;
						if(ExcelConstants.PIC.equals(content))pic_col=col;
						if(ExcelConstants.RETAIL.equals(content))retail_col=col;
						if(ExcelConstants.QTY.equals(content))qty_col=col;
					}
					if(article_number_col==-1)continue;
				}
				
			}
			if(article_number_col==-1)return "excel格式不规范，不存在article_number列";
			
			for (int rowNum = titleRow+1; rowNum <= sheet.getLastRowNum(); rowNum++) {
				QuotationMerchandiseDTO dto = new QuotationMerchandiseDTO();
				String articleNum =  ExcelUtils.getValue(sheet.getRow(rowNum), article_number_col);
				dto.setArticleNumber(articleNum);
				/*if("".equals(articleNum)){
					break;
				}*/
				if(name_col!=-1){
					String content =  ExcelUtils.getValue(sheet.getRow(rowNum), name_col);
					dto.setName(content);
				}
				if(pic_col!=-1&&picMap!=null&&picMap.size()>0){
					//String content =  ExcelUtils.getValue(sheet.getRow(rowNum), pic_col);
					String picName = "0_"+rowNum+"_"+pic_col;
					if(null!= picMap.get(picName)){
						String ext = picMap.get(picName).suggestFileExtension();
	//					dto.setPicture(ExcelConstants.EXCEL_MIDDLEPATH_PIC+picName+"."+ext);
						dto.setPicture(excelname+"/"+picName+"."+ext);
						
					}
				}
				if(qty_col!=-1){
					String content =  ExcelUtils.getValue(sheet.getRow(rowNum), qty_col);
					dto.setQty(content);
				}
				if(retail_col!=-1){
					String content =  ExcelUtils.getValue(sheet.getRow(rowNum), retail_col);
					dto.setRetail(content);
				}
				dto.setStatus(CommonConstant.MERCHANDISE_UNTREATED);
				dto.setSkuStatus(CommonConstant.SKU_UNTREATED);
				dto.setRowIndex(rowNum);
				quotMerchlist.add(dto);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			return e.getMessage();
		}
		return "";
	}
	public static  String updateMiddleExcel(QuotationMerchandiseDTO exportDTO,
			String workName,String path) throws Exception {
		try {
			Workbook book = null;
			try {
				book = new XSSFWorkbook(new FileInputStream(path + workName));
			} catch (Exception ex) {
				book = new HSSFWorkbook(new FileInputStream(path + workName));
			}
			if(book==null){
				return "excel不存在";
			}
			Sheet sheet = book.getSheetAt(0);
			Row row = sheet.getRow(exportDTO.getRowIndex());

						CellStyle styleRow = book.createCellStyle();
			styleRow.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
			
			ExcelUtils.geneCell(row, ExcelConstants.SHORT0, exportDTO.getSkuStatus()+"", styleRow);
			ExcelUtils.geneCell(row, ExcelConstants.SHORT1, ExcelUtils.getValue(row, ExcelConstants.SHORT1), styleRow);
			ExcelUtils.geneCell(row, ExcelConstants.SHORT2, ExcelUtils.getValue(row, ExcelConstants.SHORT2), styleRow);
			ExcelUtils.geneCell(row,ExcelConstants.SHORT3, ExcelUtils.getValue(row, ExcelConstants.SHORT3), styleRow);
			ExcelUtils.geneCell(row,ExcelConstants.SHORT4, ExcelUtils.getValue(row, ExcelConstants.SHORT4), styleRow);
			ExcelUtils.geneCell(row,ExcelConstants.SHORT5, ExcelUtils.getValue(row, ExcelConstants.SHORT5), styleRow);
			ExcelUtils.geneCell(row, ExcelConstants.SHORT6, exportDTO.getRate()+"", styleRow);
			ExcelUtils.geneCell(row, ExcelConstants.SHORT7, exportDTO.getEuropePrice()+"", styleRow);
			ExcelUtils.geneCell(row, ExcelConstants.SHORT8, exportDTO.getEuropePriceRmb()+"", styleRow);
			ExcelUtils.geneCell(row, ExcelConstants.SHORT9, exportDTO.getOfficePrice()+"", styleRow);
			ExcelUtils.geneCell(row, ExcelConstants.SHORT10, exportDTO.getEuropeOfficeDiscount()+"", styleRow);
			ExcelUtils.geneCell(row, ExcelConstants.SHORT11, exportDTO.getJingdongPrice()+"", styleRow);
			ExcelUtils.geneCell(row, ExcelConstants.SHORT12, exportDTO.getSkuPrice()+"", styleRow);
			ExcelUtils.geneCell(row, ExcelConstants.SHORT13, exportDTO.getRatio1()+"", styleRow);
			ExcelUtils.geneCell(row, ExcelConstants.SHORT14, exportDTO.getDiscountPrice()+"", styleRow);
			ExcelUtils.geneCell(row, ExcelConstants.SHORT15, exportDTO.getRatio2()+"", styleRow);
			ExcelUtils.geneCell(row, ExcelConstants.SHORT16, exportDTO.getReferenceInternetUrl()+"", styleRow);
			ExcelUtils.geneCell(row, ExcelConstants.SHORT17, exportDTO.getMerchandiseName(), styleRow);
			ExcelUtils.geneCell(row, ExcelConstants.SHORT18, exportDTO.getMerchandiseColor(), styleRow);
			ExcelUtils.geneCell(row, ExcelConstants.SHORT19, exportDTO.getMerchandiseCollection(), styleRow);
			ExcelUtils.geneCell(row, ExcelConstants.SHORT20, exportDTO.getMerchandiseSpecDesc(), styleRow);
			ExcelUtils.geneCell(row, ExcelConstants.SHORT21, exportDTO.getMerchandiseDesc(), styleRow);
			ExcelUtils.geneCell(row, ExcelConstants.SHORT22, exportDTO.getRemark(), styleRow);
			ExcelUtils.geneCell(row,ExcelConstants.SHORT23, exportDTO.getStatus()+"", styleRow);

			FileOutputStream fileOut = new FileOutputStream(new File(path+workName));
			 book.write(fileOut);  
             fileOut.close();  
             
		} catch (Exception e) {
			throw new Exception(e);
		}
		return  "";
				 
	}
	public static  String modifyMerchandiseStatu(QuotationMerchandiseDTO exportDTO,
			String workName,String path) throws Exception {
		try {
			Workbook book = null;
			try {
				book = new XSSFWorkbook(new FileInputStream(path + workName));
			} catch (Exception ex) {
				book = new HSSFWorkbook(new FileInputStream(path + workName));
			}
			if(book==null){
				return "excel不存在";
			}
			Sheet sheet = book.getSheetAt(0);
			Row row = sheet.getRow(exportDTO.getRowIndex());
			if(exportDTO.getStatus()!=null)row.getCell(ExcelConstants.SHORT23).setCellValue(exportDTO.getStatus()+"");
			if(exportDTO.getSkuStatus()!=null)row.getCell(ExcelConstants.SHORT0).setCellValue(exportDTO.getSkuStatus()+"");
			if(exportDTO.getSummarySkuQty()!=null)row.createCell(ExcelConstants.INT27).setCellValue(exportDTO.getSummarySkuQty()+"");
			FileOutputStream fileOut = new FileOutputStream(new File(path+workName));
			book.write(fileOut);  
            fileOut.close();  
             
		} catch (Exception e) {
			throw new Exception(e);
		}
		return  "";
				 
	}
	/**
	 * 将某个中间excel文件中的某些行数据
	 * 提取出商品及相关信息
	 */
	public static List<MerchandiseDTO>  readMerchandise(Quotation quotation,
			String[] idsArr,String statu,Map<String,Integer> stockQtyMap)throws Exception{
		
		List<MerchandiseDTO> merchDtoList = new ArrayList<MerchandiseDTO>();
		String excelName = quotation.getQuotationLink().replace(ExcelConstants.EXCEL_SAVEPATH, "");
		String middelExcel = ExcelConstants.EXCEL_MIDDLEPATH + excelName;
		Workbook book = null;
		if (excelName.indexOf(".xlsx") == -1) {
			book = new HSSFWorkbook(new FileInputStream(middelExcel));
		} else {
			book = new XSSFWorkbook(new FileInputStream(middelExcel));
		}
		if (book == null) {
			return null;
		}
		Sheet sheet = book.getSheetAt(0);

		// 处理第一行，头
		// Row hssfRow = sheet.getRow(0);
		// if (hssfRow == null) {
		// return null;
		// }
		Map<String,Integer> stockSkuQtyMap = new HashMap<String,Integer>();
		for (int i = 0; i < idsArr.length; i++) {
			if ("".equals(idsArr[i]))
				continue;
			int rowNum = Integer.parseInt(idsArr[i]);

			MerchandiseDTO merchDto = new MerchandiseDTO();
			String retail = ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT5);
			String status = ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT23);
			String skustatus = ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT0);
			String rate = ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT6);
			String europePrice = ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT7);
			String europePricermb = ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT8);
			String officePrice = ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT9);
			String jdPrice = ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT11);
			String skuPrice = ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT12);
			String discountPrice = ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT14);
			String ratio1 = ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT13);
			String ratio2 = ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT15);
			
			String merchandiseId =  ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT24);
			String skuId = ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT25);
			String priceId =  ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT26);
			if("".equals(merchandiseId)||"".equals(skuId)||"".equals(priceId)){
				merchDto.setNewMerch(true);
			}else{
				merchDto.setNewMerch(false);
			}
			
			if ("4".equals(statu) && !statu.equals(status)) {// 发布
				skustatus = CommonConstant.SKU_UNTREATED+"";
			}
			if ("2".equals(statu)) {// 囤货
				skustatus = CommonConstant.SKU_STOCKPILING+"";
			}
			
			Merchandise merch = new Merchandise();
			// merch.setMerchandiseBrand(quotation.getBrandId());
			merch.setMerchandiseCollection(ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT19));
			merch.setMerchandiseColor(ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT18));
			merch.setMerchandiseComments(ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT22));
			merch.setMerchandiseDesc(ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT21));
			if("".equals(merchandiseId))
				merch.setMerchandiseId(UUID.randomUUID().toString());
			else
				merch.setMerchandiseId(merchandiseId);
			// merch.setMerchandiseLongDescUrl(ExcelUtils.getValue(sheet.getRow(rowNum),ExcelConstants.INT15));
			merch.setMerchandiseNameCh(ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT17));
			merch.setMerchandiseNameEn(ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT2));
			// merch.setMerchandisePicNum(1);
			// merch.setMerchandisePicUrl(ExcelUtils.getValue(sheet.getRow(rowNum),ExcelConstants.INT3));
			merch.setMerchandiseSpecDesc(ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT20));
			merch.setQuotationId(quotation.getQuotationId());

			Sku merchSku = new Sku();
			merchSku.setMerchandiseId(merch.getMerchandiseId());
			if("".equals(skuId))
				merchSku.setSkuId(UUID.randomUUID().toString());
			else
				merchSku.setSkuId(skuId);
			merchSku.setSkuPartNumber(ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT1));
			merchSku.setSkuPicNum(1);
			merchSku.setSkuPicUrl(ExcelUtils.getValue(sheet.getRow(rowNum), ExcelConstants.INT3));
			merchSku.setSkuPrice("".equals(retail) ? 0 : Double.parseDouble(retail));
			merchSku.setSkuPriceDiscount(quotation.getQuotationDiscount());
			merchSku.setSkuPriceDomestic("".equals(officePrice) ? 0 : Double.parseDouble(officePrice));
			merchSku.setSkuPriceJd("".equals(jdPrice) ? 0 : Double.parseDouble(jdPrice));
			merchSku.setSkuPriceOri("".equals(europePrice) ? 0 : Double.parseDouble(europePrice));
			merchSku.setSkuStatus("".equals(skustatus) ? CommonConstant.SKU_UNTREATED : Integer.parseInt(skustatus));
			merchSku.setSkuTime(new Date());
			
			if(null != stockQtyMap&&stockQtyMap.size()>0){
				stockSkuQtyMap.put(merchSku.getSkuId(), stockQtyMap.get(rowNum+""));
			}
			
			Price merchPrice = new Price();
			merchPrice.setPriceChannel("");
			merchPrice.setPriceDiscount(Double.parseDouble(discountPrice));
			if("".equals(priceId))
				merchPrice.setPriceId(UUID.randomUUID().toString());
			else
				merchPrice.setPriceId(priceId);
			merchPrice.setPricePrice(Double.parseDouble(skuPrice));
			merchPrice.setSkuId(merchSku.getSkuId());

			merchDto.setMerch(merch);
			merchDto.setMerchPrice(merchPrice);
			merchDto.setMerchSku(merchSku);
			merchDto.setRowNum(rowNum);

			merchDtoList.add(merchDto);
		}
		stockQtyMap.clear();
		stockQtyMap.putAll(stockSkuQtyMap);
		
		return merchDtoList;
	}
	
//	更新某中间表的部分行的状态--发布
	public static void modifyPush(Quotation quotation,
			String statu,List<MerchandiseDTO> merchDtoList) throws Exception{

		String excelName = quotation.getQuotationLink().replace(ExcelConstants.EXCEL_SAVEPATH, "");
		String middelExcel = ExcelConstants.EXCEL_MIDDLEPATH + excelName;
		Workbook book = null;
		if(excelName.indexOf(".xlsx")==-1){
			book = new HSSFWorkbook(new FileInputStream(middelExcel));
		}else{
			book = new XSSFWorkbook(new FileInputStream(middelExcel));
		}
		Sheet sheet = book.getSheetAt(0);

		for (int i = 0; i < merchDtoList.size(); i++) {
			int rowNum = merchDtoList.get(i).getRowNum();
			// 更新中间excel表的处理状态
			if("4".equals(statu)){//发布
				Cell cell = sheet.getRow(rowNum).getCell(ExcelConstants.SHORT23);
				cell.setCellValue(CommonConstant.MERCHANDISE_ONSALE + "");
			}
			if("2".equals(statu)){//囤货
				sheet.getRow(rowNum).getCell(ExcelConstants.INT0).setCellValue(CommonConstant.SKU_STOCKPILING+"");
			}
			String priceId = merchDtoList.get(i).getMerchPrice().getPriceId();
			String skuId = merchDtoList.get(i).getMerchSku().getSkuId();
			String merchandiseId = merchDtoList.get(i).getMerch().getMerchandiseId();
			if(merchandiseId!=null&&!"".equals(merchandiseId))
				sheet.getRow(rowNum).createCell(ExcelConstants.INT24).setCellValue(merchandiseId);
			if(skuId!=null&&!"".equals(skuId))
				sheet.getRow(rowNum).createCell(ExcelConstants.INT25).setCellValue(skuId);
			if(priceId!=null&&!"".equals(priceId))
				sheet.getRow(rowNum).createCell(ExcelConstants.INT26).setCellValue(priceId);
		}
		FileOutputStream fileOut = new FileOutputStream(new File(middelExcel));
		book.write(fileOut);
		fileOut.close();
            
	}
	/**
	 * 从原始excel读取所有数据到map返回
	 * 将数量清空并保存在excel订单中间excel
	 * @param excellink
	 * @param excelname
	 * @param titles
	 * @param merchlist
	 * @return map{key：titles；rows；message；}
	 */
	public static  Map readExcel(String excellink,String excelname){
		Map result = new HashMap();
		try {
			Workbook book = null;
			String[] titles;
			List<String[]> merchlist = new ArrayList<String[]>();
			try {
				if(excelname.indexOf(".xlsx")==-1){
					book = new HSSFWorkbook(new FileInputStream(excellink + excelname));
				}else{
					book = new XSSFWorkbook(new FileInputStream(excellink + excelname));
				}
			} catch (Exception ex) {
				ex.printStackTrace();
				result.put("message", "excel不存在或文件破损");
				return result;
			}
			if(book==null){
				result.put("message", "excel不存在");
				return result;
			}
			Sheet sheet = book.getSheetAt(0);
			
			Map<String, PictureData>  picMap = null;
			if(excelname.indexOf(".xlsx")==-1){
				picMap = ExcelUtils.getSheetPictrues03(0, (HSSFSheet)sheet,(HSSFWorkbook)book,excelname);
			}else{
				picMap = ExcelUtils.getSheetPictrues07(0, (XSSFSheet)sheet,(XSSFWorkbook)book,excelname);
			}
			
//			处理第一行，头
			Row titleRow = sheet.getRow(0);
			if (titleRow == null) {
				result.put("message", "excel为空");
				return result;
			}
			
			titles = new String[titleRow.getPhysicalNumberOfCells()];	
			int article_number_col =-1;
			int pic_col = -1;
			int qty_col = -1;
			int retail_col = -1;
			for(int col = 0;col<titleRow.getPhysicalNumberOfCells();col++){
				String content = ExcelUtils.getValue(titleRow, col);
				if(ExcelConstants.ARTICLE_NUMBER.equals(content))article_number_col=col;
				if(ExcelConstants.PIC.equals(content))pic_col=col;
				if(ExcelConstants.QTY.equals(content))qty_col=col;
				if(ExcelConstants.RETAIL.equals(content))retail_col=col;
				titles[col]=content;
			}
			if(article_number_col==-1){
				result.put("message","excel格式不规范，不存在article_number列");
				return result;
			}
			
			for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
				String[] rowStr = new String[titles.length];
				Row row = sheet.getRow(rowNum);
				/*if("".equals(ExcelUtils.getValue(row, article_number_col))){
					break;
				}*/
				for(int c = 0;c<titles.length;c++){
					if(pic_col==c&&picMap!=null&&picMap.size()>0){
						String picName = "0_"+rowNum+"_"+pic_col;
						if(null!= picMap.get(picName)){
							String ext = picMap.get(picName).suggestFileExtension();
							rowStr[c] = excelname+"/"+picName+"."+ext;
							if(ExcelConstants.PIC_EMF_EXT.indexOf(ext) != -1){
								String newPicPath = excelname+"/"+picName+ExcelConstants.PIC_EXT;
								File file = new File(ExcelConstants.EXCEL_MIDDLEPATH_PIC+newPicPath);
								if(file.exists()){
									rowStr[c] = newPicPath;
								}
							}
						}
					}else if(qty_col==c){
//						rowStr[c] = "";
					} else{
						Cell cell = row.getCell(c);
						if(null != cell){
							cell.setCellType(Cell.CELL_TYPE_STRING);
							String content =  cell.getStringCellValue();
//							String content =  ExcelUtils.getValue(row, c);
							rowStr[c] = content;
						}
					}
					
				}
				merchlist.add(rowStr);
			}
            result.put("rows", merchlist);
            result.put("titles", titles);
            result.put("pic_col", pic_col+""); 
            result.put("qty_col", qty_col+"");
            result.put("retail_col", retail_col+"");
		} catch (Exception e) {
			e.printStackTrace();
			result.put("message",e.getMessage());
		}
		return result;
	
	}
	private  static void copyExcelToOrder(String oldLink,String oldName,String newLink,String newName) throws Exception{
		FileInputStream fis = new FileInputStream(oldLink + oldName);
		File file = new File(newLink);
		if (!file.exists()) {
			file.mkdirs();
		}
		FileOutputStream fos = new FileOutputStream(newLink+ newName);
		int len = 0;
		byte[] buf = new byte[1024];
		while ((len = fis.read(buf)) != -1) {
			fos.write(buf, 0, len);
		}
		fis.close();
		fos.close();
	}
	
	public static String saveOrder(String excellink,String excelname,String[] params){
		try {
			String orderlink = ExcelConstants.EXCEL_ORDERPATH+excelname+"/";
			String[] namesplit = excelname.split("\\.");
			String orderExcelName = namesplit[0]+"_"+new SimpleDateFormat("yyyyMMddHHmmss").format(new Date())+"."+namesplit[1];
			copyExcelToOrder(excellink,excelname,orderlink, orderExcelName);
			
			Workbook book = null;
			try {
				if(excelname.indexOf(".xlsx")==-1){
					book = new HSSFWorkbook(new FileInputStream(orderlink + orderExcelName));
				}else{
					book = new XSSFWorkbook(new FileInputStream(orderlink + orderExcelName));
				}
			} catch (Exception ex) {
				ex.printStackTrace();
				return "excel不存在或文件破损";
			}
			if(book==null){
				return "excel不存在";
			}
			Sheet sheet = book.getSheetAt(0);
			
			int qty_col = -1;
			for(int col = 0;col<sheet.getRow(0).getPhysicalNumberOfCells();col++){
				String content = ExcelUtils.getValue(sheet.getRow(0), col);
				if(ExcelConstants.QTY.equals(content))qty_col=col;
			}
			if(qty_col==-1){
				return "Excel表格错误，不存在约定名称["+ExcelConstants.QTY+"]的数量列";
			}
			for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
				Cell cell = sheet.getRow(rowNum).getCell(qty_col);
				if(null !=cell)cell.setCellValue("");
			}
			for(int i =0;i<params.length;i++){
				String[] param = params[i].split(":");
				System.out.println(i+"::"+param);
				sheet.getRow(Integer.parseInt(param[0])).getCell(qty_col)
					.setCellValue(param[1]);
			}
			
			FileOutputStream fileOut = new FileOutputStream(new File(orderlink + orderExcelName));
			book.write(fileOut);  
            fileOut.close(); 
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println(e.getMessage()+e);
			return "发生异常";
		}
		return "";
	}
	
	public static String importOrder(HttpServletResponse response,String excellink,String excelname,
			String[] params,int qty_col,int pic_col,int retail_col){
		try {
			Map map = readExcel(excellink, excelname);
			
			String[] headTitle = (String[])map.get("titles");
			List<String[]> rowsData = (List<String[]>)map.get("rows");
			
			Map<Integer,String> paramMap = new HashMap<Integer,String>();
			for(int i =0;i<params.length;i++){
				String[] param = params[i].split(":");
				paramMap.put(Integer.parseInt(param[0]), param[1]);
			}
			String[] titles = Arrays.copyOf(headTitle, headTitle.length+1);
			titles[titles.length-1]="Total_Retail";
			
			String orderExcelName = excelname.substring(0,excelname.lastIndexOf("."))
					+"_"+new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
			
			Map<String, Object> workMap =null;
//			if(excelname.indexOf(".xlsx")==-1){
				orderExcelName += ".xls";
				workMap = ExcelUtils.geneHSSFBookBegin(orderExcelName, titles);
//			}else{
//				orderExcelName += ".xlsx";
//				workMap = ExcelUtils.geneXSSFBookBegin(orderExcelName, headTitle);
//			}
			
			
			Workbook workbook = (Workbook) workMap.get("workbook");
			Sheet sheet = (Sheet) workMap.get("sheet");
			sheet.setColumnWidth(pic_col, 20*256);
			Row row = (Row) workMap.get("row");
			// 能否写多张图片，关键在与 patriarch 这个变量。写多张图片时，应该时一个对象，不应该每次多新创建对象
			Drawing patriarch = sheet.createDrawingPatriarch();
			
			int realRowNum = 1;
			for (int i = 1; i < rowsData.size(); i++) {
				if(paramMap.containsKey(i)){
					String[] rowData = rowsData.get(i-1);
					CellStyle styleRow = workbook.createCellStyle();
					styleRow.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
					row = sheet.createRow(realRowNum);
					row.setHeight((short) 2000);
					for(int col =0;col<rowData.length;col++){
						if(col==qty_col){
							ExcelUtils.geneCell(row, (short) col, paramMap.get(i), styleRow);
						}else if(col==pic_col){
							String picUrl = ExcelConstants.EXCEL_MIDDLEPATH_PIC+rowData[col];
							if(!"".equals(rowData[col])&&rowData[col] !=null){
								String imgExt = rowData[col].substring(rowData[col].lastIndexOf(".")+1, rowData[col].length());
								if(imgExt.indexOf(ExcelConstants.PIC_EMF_EXT)==-1)
									setImageToCell(patriarch, workbook, (short) pic_col, realRowNum , (short) (pic_col+1), realRowNum+1, picUrl,imgExt);
							}
						}else{
							ExcelUtils.geneCell(row, (short) col, rowData[col], styleRow);
						}
					}
					if(retail_col!=-1){
						try{
							Double retail = Double.parseDouble(rowData[retail_col]);
							Double qty = Double.parseDouble(paramMap.get(i));
							Double totalRetail = Math.floor(retail*qty*100)/100;
							ExcelUtils.geneCell(row, (short) rowData.length, totalRetail.toString(), styleRow);
						}catch(Exception e){
							ExcelUtils.geneCell(row, (short) rowData.length, "0", styleRow);
						}
					}
					realRowNum++;
				}
			}
			File file = new File(ExcelConstants.EXCEL_ORDERPATH+excelname+"/");
			if(!file.exists()){
				file.mkdirs();
			}
			FileOutputStream fileOut = new FileOutputStream(ExcelConstants.EXCEL_ORDERPATH+excelname+"/"+orderExcelName);  
			workbook.write(fileOut);  
            fileOut.close(); 

            ExcelUtils.writeBookEnd(orderExcelName, response, workbook);
		} catch (Exception e) {
			e.printStackTrace();
			return "发生异常";
		}
		return "";
	}

	private static void setImageToCell(Drawing patriarch ,Workbook wb,short col1, int row1, short col2, int row2, 
			String imagePath,String imgExt) throws IOException {

		BufferedImage bufferImg = null;
		ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();

		try {
			bufferImg = ImageIO.read(new File(imagePath));
			ImageIO.write(bufferImg, imgExt, byteArrayOut);
			int heightY = bufferImg.getHeight();
			int widthX = bufferImg.getWidth();
			int maxX = 1023;
			int maxY = 255;
			if(heightY > widthX){
				widthX = widthX* maxX /heightY ;
				heightY = maxY;
			}else if(widthX > heightY){
				heightY = heightY* maxY / widthX;
				widthX = maxX;
			}else{
				heightY = maxY;
				widthX = maxX;
			}
			ClientAnchor anchor = null;
			if(wb instanceof HSSFWorkbook){
//				anchor = new HSSFClientAnchor(0, 0, 0, 0, col1, row1, col2, row2);
				anchor = new HSSFClientAnchor(0, 0, widthX, heightY, col1, row1, col1, row1);
			}else{
//				anchor = new XSSFClientAnchor(0, 0, 0, 0, col1, row1, col2, row2);
				anchor = new XSSFClientAnchor(0, 0, widthX, heightY, col1, row1, col1, row1);
			} 
			anchor.setAnchorType(3);

			patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), Workbook.PICTURE_TYPE_PNG));

		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	public static String downloadExcel(HttpServletResponse response,
			String excellink,String excelname){
		try {
			Workbook book = null;
			try {
				if(excelname.indexOf(".xlsx")==-1){
					book = new HSSFWorkbook(new FileInputStream(excellink + excelname));
				}else{
					book = new XSSFWorkbook(new FileInputStream(excellink + excelname));
				}
			} catch (Exception ex) {
				ex.printStackTrace();
				return "excel不存在或文件破损";
			}
			if(book==null){
				return "excel不存在";
			}
			
            ExcelUtils.writeBookEnd(excelname, response, book);
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println(e.getMessage()+e);
			return "发生异常";
		}
		return "";
	}
	
	
}
